package org.example.user.manage.repository;

import org.example.commons.entity.SysAdmin;
import org.example.commons.entity.SysUser;
import org.example.user.manage.form.SysUserSearchForm;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;


/**
 * 住户 数据层
 *
 * @author bothin
 */
@Repository
public interface SysUserRepository extends JpaRepository<SysUser,Long>{

    @Query("select user.password from SysUser user " +
            "where user.userId = :id")
    String getPassword(Long id);

    @Modifying
    @Transactional
    @Query("update SysUser user set user.password = :password " +
            "where user.userId = :id")
    void setPassword(Long id, String password);

    @Query(value = "SELECT * FROM sys_user  where DATE_SUB(CURDATE(), INTERVAL ? DAY) >= date(create_time)",nativeQuery =true)
    List<SysUser>  findLastUsers( Integer days);

    SysUser findByOpenid(String openid);

    SysUser findByPhone(String phone);

    SysUser findByPhoneAndStatus(String phone, Integer status);

    List<SysUser> findByUserIdIn(Long[] ids);

    /**
     * 条件查询
     * @param form
     * @param pageable
     * @return
     */
    @Query(value = "select u from SysUser u " +
            "where u.name like :#{#form.name} and u.openid like :#{#form.openid} " +
            " and u.address like :#{#form.address} and u.phone like :#{#form.phone} " +
            " and u.houseInfo like :#{#form.houseInfo} and u.idCard like :#{#form.idCard} " +
            " and (:#{#form.sex} IS NULL or u.sex = :#{#form.sex} ) " +
            " and (:#{#form.status} IS NULL or u.status = :#{#form.status} ) " +
            "and ( :#{#form.dates[0]} IS NULL OR :#{#form.dates[1]} IS NULL OR " +
            "(u.createTime between :#{#form.dates[0]} and :#{#form.dates[1]})) ")
    Page<SysUser> searchList(@Param("form") SysUserSearchForm form, Pageable pageable);
}
